package com.citytransport.prokopjuk.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import org.apache.log4j.Logger;



import com.citytransport.prokopjuk.connectDB.ConnectionPool;
import com.citytransport.prokopjuk.vehicleDTO.Tram;

public class DAOTram implements IDAOTram{
    private ConnectionPool pool;
    private DAOVehicle daoVehicle;
    static Logger logger = Logger.getLogger(DAOTram.class.getName());
    public DAOTram(){
	pool = ConnectionPool.getConnectionPool();
	daoVehicle = new DAOVehicle();
    }
    public void creat(Tram tram) {
	Connection con = pool.getConnection();
	PreparedStatement prepStmt = null;
	try {
	    daoVehicle.create(tram);
	    prepStmt = con.prepareStatement("insert into tram values(?,?,?)");
	    prepStmt.setInt(1, tram.getID());
	    prepStmt.setBoolean(2, tram.isSpeedy());
	    prepStmt.setInt(3, tram.getAmountVagon());
	    prepStmt.executeUpdate();
	} catch (SQLException e) {
	    logger.error(e);
	}finally{
	    pool.returnConnecction(con);
	}
    }

    public void delete(Tram tram) {
	Connection con = pool.getConnection();
	PreparedStatement prepStmt = null;
	try {
	    prepStmt = con.prepareStatement("delete from tram where tramID = ?");
	    prepStmt.setInt(1, tram.getID());
	    prepStmt.executeUpdate();
	    daoVehicle.delete(tram);
	} catch (SQLException e) {
	    logger.error(e);
	}finally{
	    pool.returnConnecction(con);
	}	
    }
    
    public LinkedList<Integer> getAllTramID(){
	Connection con = pool.getConnection();
	PreparedStatement prepStmt = null;
	ResultSet set = null;
	   LinkedList<Integer> index = new LinkedList<Integer>();
	    try {
		prepStmt = con.prepareStatement("select tram.tramID from tram");
		 set = prepStmt.executeQuery();
		    while (set.next()) {
			index.add(set.getInt("tramID"));
		    }
	    } catch (SQLException e) {
		logger.error(e);
	    }finally{
		pool.returnConnecction(con);
	    }
	   return index;
    }

    public void deleteAll() {
	Connection con = pool.getConnection();
	try {
	    Statement stmt = con.createStatement();
	    stmt.executeUpdate("delete from tram");
	} catch (SQLException e1) {
	    logger.error(e1);
	}finally{
	    pool.returnConnecction(con);
	}
	LinkedList<Integer> index = new LinkedList<Integer>();
	index = getAllTramID();
	for(int e: index){
	    daoVehicle.delete(e);
	}
    }

    public Tram read(int tramID) {
	Tram tram = null;
	Connection con = pool.getConnection();
	PreparedStatement prepStmt = null;
	HashMap<String,String> atr = new HashMap<String, String>();
	atr = daoVehicle.read(tramID);
	try {
	    prepStmt = con.prepareStatement("select * from tram where tramID = ?");
	    prepStmt.setInt(1, tramID);
	    ResultSet rezSet = prepStmt.executeQuery();
	    rezSet.next();
	    tram = new Tram(Integer.parseInt(atr.get("vehicleID")),Integer.parseInt(atr.get("seatAmount")),
		    atr.get("registrationNumber"),atr.get("model"),
		    rezSet.getBoolean("speedy"), rezSet.getInt("amountVagon"));
	} catch (SQLException e) {
	    logger.error(e);
	}finally{
	    pool.returnConnecction(con);
	}	
	return tram;
    }

    public ArrayList<Tram> readAll() {
	LinkedList<Integer> index = new LinkedList<Integer>();
	index = getAllTramID();
	ArrayList<Tram> tramList= new ArrayList<Tram>();
	for(int e : index){
	    tramList.add(read(e));
	}
	return tramList;
    }

    public void update(Tram tram, int seatAmount, String registrationNumber,
	    String model, int routeID, boolean speedy, int amountVagon) {
	Connection con = pool.getConnection();
	PreparedStatement prepStmt = null;
	daoVehicle.update(tram, seatAmount, registrationNumber, model, routeID);
	try {
	    prepStmt = con.prepareStatement("update tram set speedy=?, amountVagon = ? where tramID = ?");
	    prepStmt.setBoolean(1, speedy);
	    prepStmt.setInt(2, amountVagon);
	    prepStmt.setInt(3, tram.getID());
	    prepStmt.executeUpdate();
	} catch (SQLException e) {
	    logger.error(e);
	}finally{
	    pool.returnConnecction(con);
	}	
    }
    private LinkedList<Integer> getTramIDAtRoute(int routeID){
	LinkedList<Integer> index = new LinkedList<Integer>();
	Connection con = pool.getConnection();
	try {
	    PreparedStatement prepStmt= con.prepareStatement(
		    "Select tramID from tram where tramID in " +
		"(select vehicleID from vehicle where routeID = ?)");;
		prepStmt.setInt(1, routeID);
	    ResultSet resSet = prepStmt.executeQuery();
	    while(resSet.next()){
		index.add(resSet.getInt("tramID"));
	    }	    
	} catch (SQLException e) {
	    logger.error(e);
	}finally{
	    pool.returnConnecction(con);	    
	}
	
	return index;
    }
    
    public ArrayList<Tram> readAtRoute(int routeID) {
	LinkedList<Integer> a = new LinkedList<Integer>(getTramIDAtRoute(routeID));
	ArrayList<Tram> trams = new ArrayList<Tram>();
	for(int index: a){
	    trams.add(read(index));
	}	
	return trams;
    }
}
